package Domain.MySqlAccessors;

import Domain.Recommendation;
import Domain.IRecommendationSet;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.LinkedList;

import Persistance.MySqlUtilityManagment;
import Persistance.PrintfFormat;
import BusinessLogic.Exceptions.DataAlreadyExistException;

public class MySqlRecommendationProxy extends MySqlProxy<Recommendation> implements IRecommendationSet {
    
    public MySqlRecommendationProxy(MySqlUtilityManagment sql) {
        super(sql);
        TABLE_NAME = "recommendations";
        KEY_COLUMN_NAME = "username='%s' AND moviename='%s'";
        ALL_COLUMN_NAMES = "username, moviename, description, rank, date";
    }

    public Recommendation toRecordType(ResultSet rs) throws SQLException {
        String username = (String) rs.getObject("username");
        String moviename = (String) rs.getObject("moviename");
        String description = (String) rs.getObject("description");
        int rank = ((Long) rs.getObject("rank")).intValue();
        String date = (String) rs.getObject("date");
        return new Recommendation(username,moviename,description,rank,date);
    }

    public String toValues(Recommendation r) {
        Object[] values = {r.getUserName(),r.getMovieName(),r.getDescription(),r.getUserRank(),r.getDate()};
        return new PrintfFormat("'%s','%s','%s',%d,'%s'").sprintf(values);
    }

    public Recommendation getRecord(String user, String item) {
        return getRecord(user+","+item);
    }

    public void deleteRecord(String userId, String movieId) {
        deleteRecord(userId+","+movieId);
    }

    public double getRank(String user, String item) {
        return get(user,item).getUserRank();
    }

    public double getAvgRank(String user) {
        return getUserRank(user);
    }

    public Collection<String> getUsers() {
        return m_sql.searchData("recommendations","username",null,"GROUP BY username");
    }

    public Collection<String> getJointItems(String user1, String user2) {
        return m_sql.searchData("recommendations r1, recommendations r2","r1.moviename","r1.moviename=r2.moviename AND r1.username='"+user1+"' AND r2.username='"+user2+"'");
    }

    public boolean hasRank(String user, String movie) {
        return (get(user,movie) != null);
    }

    public Collection<String> getUsers(String movie) {
        return m_sql.searchData("recommendations","username","moviename='"+movie+"'");
    }

    public double getMovieRank(String movie) {                
        return getAverageRank("moviename='"+movie+"'");
    }

    public double getUserRank(String user) {
        return getAverageRank("username='"+user+"'");
    }

    private double getAverageRank(String filter) {
        Collection<String> result = m_sql.searchData("recommendations","AVG (rank)",filter);                               
        if (!result.isEmpty())
            return Double.parseDouble(result.iterator().next());
        else
            return 0; // TODO: make sure 0 is right value
    }

    public Collection<String> getMostRatedMovies(int count, boolean topDown) {
        //SELECT moviename FROM recommendations GROUP BY moviename ORDER BY AVG (rank) DESC LIMIT 0,2
        String groupby = "GROUP BY moviename ORDER BY AVG (rank) ";
        if (topDown)
            groupby += "DESC ";
        groupby += "LIMIT 0,"+count;
        return m_sql.searchData("recommendations","moviename",null,groupby);
    }

    public Collection<Recommendation> getMovieItems(String movie) {
        return searchRecords("moviename='"+movie+"'");
    }

    public Recommendation get(String user, String movie) {
        return getRecord(user, movie);
    }

    public void add(Recommendation r) throws DataAlreadyExistException {
        addRecord(r);
    }

    public void remove(Recommendation r) {
        deleteRecord(r.getUserName()+","+r.getMovieName());
    }

    public Collection<Recommendation> values() {
        return searchRecords("all");
    }

    public Collection<String> getRecommendingUsers(String movie) {
        return getUsers(movie);
    }

    public Collection<Recommendation> getUserItems(String user) {
        return searchRecords("username='"+user+"'");
    }

    public String[] getUsersByMovieRating(String moviename, int rank, int start, int amount) {
        return m_sql.searchData("recommendations","username","moviename='"+moviename+"' AND rank>="+rank,start,amount);
    }

    public int getUserRecCount(String username) {
        Collection<String> result = m_sql.searchData("recommendations","COUNT (username)","username='"+username+"'");
        if (!result.isEmpty())
            return Integer.parseInt(result.iterator().next());
        else
            return 0;
    }
    
    public Collection<String> getMoviesByOrderedRate(boolean highestRate){
    	if(highestRate)
    		return m_sql.searchData("recommendations","moviename",null,"ORDER BY rank DESC");
    	else
    		return m_sql.searchData("recommendations","moviename",null , "ORDER BY rank");
    }

	public int getHighestRank(String movie) {
		Collection<String> ranks =  m_sql.searchData("recommendations","rank","moviename='"+movie+"'","ORDER BY rank DESC");
		String rank = ((LinkedList<String>)ranks).getFirst();
		return Integer.parseInt(rank); 
	}

	public int getLowestRank(String movie) {
		// TODO Auto-generated method stub
		Collection<String> ranks =  m_sql.searchData("recommendations","rank","moviename='"+movie+"'","ORDER BY rank ASC");
		String rank = ((LinkedList<String>)ranks).getFirst();
		return Integer.parseInt(rank);
	}

    public double[] getRankAvgByGenre(String user) {
        Collection<String> ans = m_sql.searchData("genres",
                "IFNULL(recs.avgRank,5)",null ,
                "LEFT OUTER JOIN (SELECT genre, avg(rank) as avgRank " +
                "                 FROM recommendations r, movieGenres mg " +
                "                 WHERE r.moviename = mg.movie " +
                "                 AND username = '"+user+"' " +
                "                 GROUP BY genre) recs " +
                "ON genres.id = recs.genre;");
        double[] res = new double[ans.size()];
        int i = 0;
        for (String val : ans) {
            res[i] = Double.parseDouble(val);
            i++;
        }
        return res;
    }
}
